package com.ys.util.codegenerate.mysql;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.bo.util.StringBoUtil;

import freemarker.template.Template;

/**
 * 描述：代码生成器 Created by Jason on 2018/07/28.
 */
public class CodeGenerateMysqlUtil {
	
	private static String packagename = ConfigUtil.packageName;
	private static String packagePath = ConfigUtil.packagePath;
	private static String DATABASE_NAME = ConfigUtil.DATABASE_NAME;
	private static String URL = ConfigUtil.URL;
	private static String USER = ConfigUtil.USER;
	private static String PASSWORD = ConfigUtil.PASSWORD;
	private static String DRIVER = ConfigUtil.DRIVER;
	private static String projectCodeArr[] = ConfigUtil.projectCodeArr;
	private static String projectCode;
	private static String projectPathCommon;
	
	private static String PATH_PACKAGE = "pathPackage";

	public Connection getConnection() throws Exception {
		Class.forName(DRIVER);
		Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
		return connection;
	}

	public static void main(String[] args) throws Exception {
		CodeGenerateMysqlUtil codeGenerateUtils = new CodeGenerateMysqlUtil();

		// 获取项目路径
		for (String str : projectCodeArr) {
			projectCode = str;
//			projectPathCommon = projectCode + "/ys_web";
			projectPathCommon = projectCode;
			File file = new File(projectCode);
			if (!file.exists()) {
				System.out.println("不存在---" + file.getPath());
			} else {
				System.out.println("存在---" + file.getPath());
				break;
			}
		}

		// 单表生成
		Map<String, String> paramMap = new HashMap<String, String>();
		// TODO，需要指定包所在的路径
		paramMap.put(PATH_PACKAGE, "business");
		codeGenerateUtils.generateSingle("temp_img_tran", paramMap);
		
//		// 生成所有
//		codeGenerateUtils.getTableList();
//		List<String> listTableAllName = codeGenerateUtils.getTableList();
//		for (int i = 0; i < listTableAllName.size(); i++) {
//			String tableAllName = listTableAllName.get(i);
//			codeGenerateUtils.generateSingle(tableAllName);
//		}
		
		
//		// 生成MAP
//		codeGenerateUtils.getTableList();
//		List<String> listTableAllName = codeGenerateUtils.getTableList();
//		for (int i = 0; i < listTableAllName.size(); i++) {
//			String tableAllName = listTableAllName.get(i);
//			if (tableAllName.contains("TBL_BANK_BIN_INF")) {
//				continue;
//			}
//			// 获取表信息
//			Table table = codeGenerateUtils.getTable(tableAllName);
////			System.out.println("put(\"" + StringUtils.uncapitalize(table.getClassname()) + "\", Conf" + table.getClassname() + ".class);");
//			System.out.println("put(\"" + StringUtils.uncapitalize(table.getClassname()) + "\", \"" + table.getPkjavaname() + "\");");
//			
//		}
		
	}

	public void generateSingle(String tableallname, Map<String, String> paramMap) throws Exception {
		try {
			
			
			// 获取表信息
			Table table = getTable(tableallname);
			
			// 获取表结构信息
			List<Column> columnlist = getColumnlist(tableallname, table);
			
			// 设置表的字段数
			table.setColumnnum(columnlist.size() + 1);
			
			Map<String, Object> dataMap = new HashMap<>();
			dataMap.put("columnlist", columnlist);
			dataMap.put("table", table);
			dataMap.put("packagename", packagename);
			dataMap.put(PATH_PACKAGE, paramMap.get(PATH_PACKAGE));
			// 生成Entity文件
			generateEntity(dataMap, table.getClassname());
//			// 生成Dto文件
//			generateDto(dataMap, table.getClassname());
			// 生成Mapperjava文件
			generateMapperjava(dataMap, table.getClassname());
			// 生成Mapperxml文件
			generateMapperxml(dataMap, table.getClassname());
//			// 生成Service文件
//			generateService(dataMap, table.getClassname());
//			// 生成ServiceImpl文件
//			generateServiceimpl(dataMap, table.getClassname());
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {

		}
	}

	private void generateController(Map<String, Object> dataMap, String classname, String projectPath) throws Exception {

		String suffix = "Controller.java";
		String dirPath = projectPath + "/src/main/java" + packagePath + "/controller/api/";
		File file = new File(dirPath);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Controller.ftl";
		File dirFile = new File(dirPath + "ApiInter" + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateDto(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = "Dto.java";
		final String path = projectPathCommon + "/src/main/java" + packagePath + "/dto/admin/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Dto.ftl";
		File dirFile = new File(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateEntity(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = ".java";
		final String path = projectPathCommon + "/src/main/java" + packagePath + "/entity/" + dataMap.get(PATH_PACKAGE) + "/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Entity.ftl";
		File dirFile = new File(path + classname + suffix);
		
		System.out.println(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateMapperjava(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = "Mapper.java";
		final String path = projectPathCommon + "/src/main/java" + packagePath + "/mapper/" + dataMap.get(PATH_PACKAGE) + "/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Mapperjava.ftl";
		File dirFile = new File(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateMapperxml(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = "Mapper.xml";
		final String path = projectPathCommon + "/src/main/resources/mapper/" + dataMap.get(PATH_PACKAGE) + "/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Mapperxml.ftl";
		File dirFile = new File(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateService(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = "Service.java";
		final String path = projectPathCommon + "/src/main/java" + packagePath + "/service/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Service.ftl";
		File dirFile = new File(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateServiceimpl(Map<String, Object> dataMap, String classname) throws Exception {

		final String suffix = "ServiceImpl.java";
		final String path = projectPathCommon + "/src/main/java" + packagePath + "/service/impl/";
		File file = new File(path);
		if (!file.exists()) {
			file.mkdirs();
		}
		final String templateName = "Serviceimpl.ftl";
		File dirFile = new File(path + classname + suffix);
		
		generateFileByTemplate(templateName, dirFile, dataMap);

	}

	private void generateFileByTemplate(String templateName, File file, Map<String, Object> dataMap)
			throws Exception {
		
//		if (file.exists()) {
//			System.out.println("已存在---" + file.getPath());
//			return;
//		}
		
		Template template = FreeMarkerTemplateUtil.getTemplate(templateName);
		FileOutputStream fos = new FileOutputStream(file);
		Writer out = new BufferedWriter(new OutputStreamWriter(fos, "utf-8"), 10240);
		template.process(dataMap, out);
	}
	
	public List<Column> getColumnlist(String tableallname, Table table) throws Exception {
		table.setHasDate(false);
		table.setHasDecimal(false);
		
		List<Column> columnlist = new ArrayList<Column>();
		
		Connection connection = getConnection();
		Statement stmt = connection.createStatement();
		ResultSet resultSet = stmt.executeQuery("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + DATABASE_NAME + "' AND TABLE_NAME = '" + tableallname + "'");
		
		System.out.println();
		
		// 字段序号值，初始值为1
		while (resultSet.next()) {
			
			System.out.println(
					"COLUMN_NAME=====" + resultSet.getString("COLUMN_NAME")
					+ ", IS_NULLABLE=====" + resultSet.getString("IS_NULLABLE")
					+ ", DATA_TYPE=====" + resultSet.getString("DATA_TYPE")
					+ ", COLUMN_TYPE=====" + resultSet.getString("COLUMN_TYPE")
					+ ", ORDINAL_POSITION=====" + resultSet.getInt("ORDINAL_POSITION")
					+ ", COLUMN_COMMENT==" + resultSet.getString("COLUMN_COMMENT")
					);
			
			String COLUMN_TYPE = resultSet.getString("COLUMN_TYPE");
			String DATA_TYPE = resultSet.getString("DATA_TYPE");
			int index_quote_befor = COLUMN_TYPE.indexOf("(");
			int index_quote_after = COLUMN_TYPE.indexOf(")");
			String COLUMN_SIZE = "0";
			if (index_quote_befor > 0 && index_quote_after > 0) {
				COLUMN_SIZE = COLUMN_TYPE.substring(index_quote_befor + 1, index_quote_after);
			}
//			String COLUMN_SIZE = COLUMN_TYPE.replace(DATA_TYPE, "").replace("(", "").replace(")", "").replace("'", "");
			int columnSize = "enum".equals(DATA_TYPE)
					|| "decimal".equals(DATA_TYPE)
					|| "double".equals(DATA_TYPE)
					? 0 : ("".equals(COLUMN_SIZE) ? 0 : Integer.parseInt(COLUMN_SIZE));
			String COLUMN_COMMENT = resultSet.getString("COLUMN_COMMENT");
			
			Column column = new Column();
			
			// 设置字段序号
			column.setColumnsortno(resultSet.getInt("ORDINAL_POSITION"));
			
			// 主键id字段略过
			String pkname = table.getPkname();
			if (resultSet.getString("COLUMN_NAME").equals(pkname))
				continue;
			// 获取数据库字段名称
			String columnjdbcname = resultSet.getString("COLUMN_NAME");
			column.setColumnjdbcname(columnjdbcname);
			// 生成java字段名称
			String columnjavaname = StringBoUtil.replaceUnderLineAndUpperCase(columnjdbcname);
			column.setColumnjavaname(columnjavaname);
			// 获取jdbc字段类型
			String columnjdbctype = resultSet.getString("DATA_TYPE").toUpperCase();
			if ("TINYINT UNSIGNED".equals(columnjdbctype)) {
				column.setColumnjdbctype("TINYINT");
			} else if ("INT".equals(columnjdbctype)) {
				column.setColumnjdbctype("INTEGER");
			} else if ("ENUM".equals(columnjdbctype)) {
				column.setColumnjdbctype("VARCHAR");
			} else if ("TEXT".equals(columnjdbctype)) {
				column.setColumnjdbctype("VARCHAR");
			} else if ("LONGTEXT".equals(columnjdbctype)) {
				column.setColumnjdbctype("VARCHAR");
			} else if ("MEDIUMTEXT".equals(columnjdbctype)) {
				column.setColumnjdbctype("VARCHAR");
			} else if ("DECIMAL".equals(columnjdbctype)) {
				column.setColumnjdbctype("DECIMAL");
			} else if ("DATETIME".equals(columnjdbctype)) {
				column.setColumnjdbctype("DATE");
			} else if ("TIME".equals(columnjdbctype)) {
				column.setColumnjdbctype("VARCHAR");
			} else {
				column.setColumnjdbctype(columnjdbctype);
			}
			// 获取java字段类型
			String columnjavatype = "";
			List<String> enumvalues = new ArrayList<String>();
			if ("VARCHAR".equals(columnjdbctype)) {
				columnjavatype = "String";
			} else if ("CHAR".equals(columnjdbctype)) {
				columnjavatype = "String";
			} else if ("INT".equals(columnjdbctype)) {
				int columnsize = columnSize;
				if (columnsize >= 11) {
					columnjavatype = "Long";
				} else {
					columnjavatype = "Integer";
				}
			} else if ("TINYINT UNSIGNED".equals(columnjdbctype)) {
				columnjavatype = "Integer";
			} else if ("TINYINT".equals(columnjdbctype)) {
				columnjavatype = "Integer";
			} else if ("BIGINT".equals(columnjdbctype)) {
				columnjavatype = "Long";
			} else if ("TEXT".equals(columnjdbctype)) {
				columnjavatype = "String";
			} else if ("MEDIUMTEXT".equals(columnjdbctype)) {
				columnjavatype = "String";
			} else if ("LONGTEXT".equals(columnjdbctype)) {
				columnjavatype = "String";
			} else if ("ENUM".equals(columnjdbctype)) {
				columnjavatype = "String";
				String[] enumvaluesArray = COLUMN_SIZE.split(",");
				for (String str : enumvaluesArray) {
					enumvalues.add(str);
				}
				
			} else if ("TIMESTAMP".equals(columnjdbctype)) {
				table.setHasDate(true);
				columnjavatype = "Date";
			} else if ("DECIMAL".equals(columnjdbctype)) {
				table.setHasDecimal(true);
				columnjavatype = "BigDecimal";
			} else if ("DOUBLE".equals(columnjdbctype)) {
				columnjavatype = "Double";
			} else if ("DATE".equals(columnjdbctype)) {
				table.setHasDate(true);
				columnjavatype = "Date";
			} else if ("DATETIME".equals(columnjdbctype)) {
				table.setHasDate(true);
				columnjavatype = "Date";
			} else if ("TIME".equals(columnjdbctype)) {
				columnjavatype = "String";
			}
			column.setColumnjavatype(columnjavatype);
			// 字段在数据库的注释
			column.setColumncomment(COLUMN_COMMENT);
			column.setEnumvalues(enumvalues);
			columnlist.add(column);
		}
		
		return columnlist;
	}
	
	public String getPkname(String tablename) throws Exception {
		String pkname = "";
		Connection connection;
		connection = DriverManager.getConnection(URL, USER, PASSWORD);
		String catalog = connection.getCatalog();
		DatabaseMetaData dbmd = connection.getMetaData();

		ResultSet primaryKeyResultSet = dbmd.getPrimaryKeys(catalog, null, tablename);
		while (primaryKeyResultSet.next()) {
			pkname = primaryKeyResultSet.getString("COLUMN_NAME");
		}
		
		return pkname;
	}
	
	public String getTableAttr(String tablename) throws Exception {
		String pkname = "";
		Connection connection;
		connection = DriverManager.getConnection(URL, USER, PASSWORD);
		String catalog = connection.getCatalog();
		DatabaseMetaData dbmd = connection.getMetaData();

		ResultSet primaryKeyResultSet = dbmd.getPrimaryKeys(catalog, null, tablename);
		while (primaryKeyResultSet.next()) {
			pkname = primaryKeyResultSet.getString("COLUMN_NAME");
		}
		
		return pkname;
	}
	
	public Table getTable(String tableAllName) throws Exception {
		Table table = new Table();
		
		table.setTableallname(tableAllName);
		
		String tablename = tableAllName;
		String pkname = getPkname(tableAllName);
		String pkjavaname = StringBoUtil.replaceUnderLineAndUpperCase(pkname);
		String classname = StringUtils.capitalize(StringBoUtil.replaceUnderLineAndUpperCase(tablename));
		
		table.setTablename(tablename);
		table.setClassname(classname);
		table.setPkname(pkname);
		table.setPkjavaname(pkjavaname);
//		System.out.println("TABLE_NAME===" + tableAllName);
		
		return table;
	}
	
	public List<String> getTableList() throws Exception {
		List<Column> columnlist = new ArrayList<Column>();
		
		Connection connection = getConnection();
		Statement stmt = connection.createStatement();
//		ResultSet resultSet = stmt.executeQuery("SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + DATABASE_NAME + "' and TABLE_NAME > 'r'");
		ResultSet resultSet = stmt.executeQuery("SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + DATABASE_NAME + "'");
		
		System.out.println();
		
		List<String> listTable = new ArrayList<String>();
		// 字段序号值，初始值为1
		while (resultSet.next()) {
			
//			System.out.println(
//					"TABLE_NAME=====" + resultSet.getString("TABLE_NAME")
//					);
			String regex = ".*[0-9].*";
			String tableAllName = resultSet.getString("TABLE_NAME");
			if (tableAllName.matches(regex)) {
				System.out.println(
						"TABLE_NAME=====" + resultSet.getString("TABLE_NAME")
						);
				continue;
			}
			listTable.add(tableAllName);
		}
		
		return listTable;
	}

	public void getTableInfo() throws SQLException {
		Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
		String catalog = connection.getCatalog();
		DatabaseMetaData dbmd = connection.getMetaData();
		ResultSet rs = null;
		String[] types = { "TABLE" };
		rs = dbmd.getTables(null, null, "%", types);

		while (rs.next()) {

			ResultSet primaryKeyResultSet = dbmd.getPrimaryKeys(catalog, null, rs.getString("TABLE_NAME"));
			while (primaryKeyResultSet.next()) {
				System.out.println(primaryKeyResultSet.getString("COLUMN_NAME"));
			}

			// String pkName = rs.getString("PK_NAME"); // 主键名
			String tableName = rs.getString("TABLE_NAME"); // 表名
			String tableType = rs.getString("TABLE_TYPE"); // 表类型
			String remarks = rs.getString("REMARKS"); // 表备注
			System.out.println(tableName + " - " + tableType + " - " + remarks);
		}
	}
}